/* QAD_Item-SiteCostReport.p - ITEM-SITE COST REPORT                          */
/* Copyright 1986-2011 QAD Inc., Santa Barbara, CA, USA.                      */
/* All rights reserved worldwide.  This is an unpublished work.               */
/* $Id:: QAD_TransactionsAccountingReport.p 1046 2010-09-10 09:08:19Z jvz  $: */
/*                                                                            */
/* Report matches legacy ppptiq03.p for use by QAD Reporting Framework.       */
/*                                                                            */

{mfsubdirs.i}
{{&US_BBI}mfdeclre.i}
{{&US_BBI}gplabel.i}

{com/qad/shell/report/dsReportRequest.i}
{com/qad/shell/report/ReportConstants.i}

/*============================================================================*/
/* ****************************  TEMP TABLES  ******************************* */
/*============================================================================*/

/* TEMP-TABLE DEFINITION BLOCK */

define temp-table tt_pt_mstr no-undo like pt_mstr
   field c_fiscal_desc as   character
   field ptal_alt_part like ptal_mstr.ptal_alt_part
   field unique_id     as   integer
.

define temp-table ttCostInfo no-undo
   field element    like spt_det.spt_element
   field cst_tl     like spt_det.spt_cst_tl
   field cst_ll     like spt_det.spt_cst_ll
   field is_primary as   logical
   field cat_desc   as   character
   field ao         like spt_det.spt_ao
   field unique_id  as   integer
   field ref_id     like tt_pt_mstr.unique_id
.

define temp-table ttTotal no-undo
   field tot_tl    like spt_det.spt_cst_tl
   field tot_ll    like spt_det.spt_cst_ll
   field cst_date  like sct_det.sct_cst_date
   field site      like pt_part.pt_site
   field csset     as   character
   field ref_id    like ttCostInfo.unique_id
.

define dataset dsReportResults for tt_pt_mstr, ttCostInfo, ttTotal.

/*============================================================================*/
/* *****************************  MAIN BLOCK  ******************************* */
/*============================================================================*/
define input parameter runReport    as logical no-undo.
define input parameter reportHandle as handle  no-undo.
define input parameter dataset      for dsReportRequest.
define output parameter dataset-handle phReportResults.

{com/qad/shell/report/reporting.i}

define variable vhDS           as handle  no-undo.
define variable unique_id_part as integer no-undo.
define variable unique_id_cost as integer no-undo.
define workfile sptwkfl
      field element  like spt_element 
      field primary  like mfc_logical 
      field prim2    like mfc_logical
      field ao       like spt_ao     
      field cst_tl   like spt_cst_tl
      field cst_ll   like spt_cst_ll
      field cst_tot  like spt_cst_tl
      field cat_desc as character   
      field seq      like spt_pct_ll
      field part     like pt_part.

empty temp-table tt_pt_mstr no-error.
empty temp-table ttCostInfo no-error.
empty temp-table ttTotal    no-error.

for first ttReportRequest no-lock:

   vhDS = dataset dsReportResults:handle.
   run FillMetaData in reportHandle(
      vhDS, "QAD_Item-SiteCostReport.meta.xml").

   run SetReportMetaData (input reportHandle).

   if runReport then do:
      run RunReport
         (output dataset-handle phReportResults).
   end.

   delete object phReportResults no-error.

end.

PROCEDURE SetReportMetaData:

   define input parameter  ipReportHandle as handle.

   run SetFieldMetaParameter in ipReportHandle(
         "tt_pt_mstr", "pt_site", "DefaultValue", global_site).
   
   run SetFieldMetaParameter in ipReportHandle(
         "tt_pt_mstr", "pt_part", "DefaultValue", global_part).

END PROCEDURE. /* PROCEDURE ModifyMetaData */

/* DATA RETRIEVING LOGIC BLOCK */
PROCEDURE RunReport:

   define output parameter dataset-handle phReportResults.

   /* RETRIEVE THE DATA FROM DATABASE */
   define variable queryString      as character no-undo.
   define variable hMainQuery       as handle    no-undo.
   define query    MainQuery        for pt_mstr, si_mstr.
   define query    MainQueryWithAlt for ptal_mstr, pt_mstr, si_mstr.

   define variable subQueryString   as character no-undo.
   define variable hSubQuery        as handle    no-undo.
   define query    SubQuery         for in_mstr.
   
   define variable part     like pt_mstr.pt_part no-undo.
   define variable site         like si_mstr.si_site no-undo.
   
   define variable frtitle as   character format "x(24)" no-undo.
   define variable l_curr_set   like in_cur_set          no-undo.
   define variable l_gl_set     like in_gl_set           no-undo.
   define variable gl_site      like in_gl_cost_site     no-undo.
   define buffer   b-in-mstr      for in_mstr.
   define variable c_fiscal_desc as character format "x(40)" no-undo.

   assign
      hMainQuery           = query MainQuery:handle
      hSubQuery            = query SubQuery:handle
   .

   for first icc_ctrl
      where icc_ctrl.icc_domain = global_domain no-lock:
   end.

   queryString = "for each pt_mstr no-lock where pt_domain = "
                   + quoter(global_domain).

   if not IsConditionEmpty("tt_pt_mstr", "pt_site")
   then do:
      assign
         hMainQuery      = query MainQueryWithAlt:handle
         queryString     = "for each ptal_mstr no-lock "
                         + " where ptal_mstr.ptal_domain = "
                         + quoter(global_domain)
                         + " and ptal_mstr.ptal_active ".

      run AddAllConditions in reportHandle
         ( "ptal_mstr" , input-output queryString ).

      queryString = queryString + " , each pt_mstr no-lock where pt_domain = "
                      + quoter(global_domain)
                      + " and pt_part = ptal_part ".
   end. /* if not IsConditionEmpty ... */

   run AddSomeConditions in reportHandle
      ( "pt_mstr", "pt_site", input-output queryString ).

   queryString = queryString + ", first si_mstr "
               + " where si_mstr.si_domain = "
               + quoter(global_domain).

   run AddSpecificConditions in reportHandle
      ( "pt_mstr", "pt_site:si_site", input-output queryString ).

   queryString = queryString + ":".

   hMainQuery:query-prepare(queryString).
   hMainQuery:query-open().
   hMainQuery:get-next().

   repeat while not hMainQuery:query-off-end:
      /* SEARCH THE FISCAL CLASS DESCRIPTION */
      {{&US_GP}gprunp.i "gpcodxr" "p" 
                        "getGeneralizedCodeDescription"
                        "(input  ""pt_fiscal_class"",
                        input  pt_fiscal_class,
                        output c_fiscal_desc)"}
      
      create tt_pt_mstr.
      buffer-copy pt_mstr to tt_pt_mstr no-error.
      assign
         unique_id_part           = unique_id_part + 1
         tt_pt_mstr.c_fiscal_desc = c_fiscal_desc
         tt_pt_mstr.unique_id     = unique_id_part
      .

      subQueryString = "for each in_mstr no-lock where in_domain = "
                     + quoter(global_domain)
                     + " and in_part = "
                     + quoter(pt_mstr.pt_part).

      run AddSpecificConditions in reportHandle
         ("pt_mstr", "pt_site:in_site", input-output subQueryString).

      subQueryString = subQueryString + ":".

      hSubQuery:query-prepare(subQueryString).
      hSubQuery:query-open().
      hSubQuery:get-next().

      repeat while not hSubQuery:query-off-end:

         if not can-find(first sct_det
            where sct_det.sct_domain = global_domain 
            and ((sct_site = in_site or sct_site = in_gl_cost_site)
            and   sct_part = pt_part))
         then do:
            hSubQuery:get-next().
            next.
         end.

         assign 
            l_gl_set   = icc_gl_set
            l_curr_set = icc_cur_set.

         if in_gl_set <> "" then l_gl_set = in_gl_set.

         if in_cur_set <> "" then l_curr_set = in_cur_set.

         gl_site = in_gl_cost_site.

         if in_site <> in_gl_cost_site 
         then do:
            for first b-in-mstr
               where b-in-mstr.in_domain = global_domain 
               and   b-in-mstr.in_site   = in_mstr.in_gl_cost_site
               and   b-in-mstr.in_part   = in_mstr.in_part 
            no-lock:
            
               if b-in-mstr.in_gl_set = "" 
               then do:
                  l_gl_set = icc_gl_set.
               end.
               else do:
                  l_gl_set = b-in-mstr.in_gl_set.
               end.
            end. /* for first b-in-mstr */
         end. /* if in_site <> in_gl_cost_site */
         
         run CreateCostDetailAndTotal(gl_site, l_gl_set).
         run CreateCostDetailAndTotal(gl_site, l_curr_set).

         hSubQuery:get-next().
      end. /* SubQuery Repeat */

      hSubQuery:query-close().
      hMainQuery:get-next().
   end. /* MainQuery REPEAT */

   hMainQuery:query-close().
   delete object hMainQuery.
   delete object hSubQuery.
   phReportResults = dataset dsReportResults:handle.
   delete object phReportResults no-error.


END PROCEDURE.

PROCEDURE CreateCostDetailAndTotal:
   define input parameter gl_site as character no-undo.
   define input parameter csset   as character no-undo.

   define variable i       as integer no-undo.
   define variable usr_val as character format "x(12)" extent 5.
   define variable usr_elm like sc_element extent 5.

   for first sct_det
      where sct_det.sct_domain = global_domain
      and sct_sim  = csset
      and sct_part = pt_part
      and sct_site = gl_site 
   no-lock:
   end.
   
   if available sct_det 
   then do:
      /* DISPLAY COST DETAILS */
      for each lngd_det no-lock
         where lngd_dataset = "sc_mstr"
           and lngd_key1 = ""
           and lngd_key2 = ""
           and lngd_key3 = ""
           and lngd_field = "sc_category"
           and lngd_lang = global_user_lang:
         if lngd_key4 = "1" then usr_val[1] = lngd_translation.
         usr_val[integer(lngd_key4)] = lngd_translation.
         usr_elm[integer(lngd_key4)] = substring(lngd_translation,1,8).
      end.

      for each sptwkfl exclusive-lock:
         delete sptwkfl.
      end.

      i = 0.
      for each spt_det no-lock
         where spt_domain = global_domain
           and spt_site   = sct_site
           and spt_sim    = sct_sim
           and spt_part   = sct_part
      by spt_pct_ll by spt_cst_tl descending by spt_element:
         i = i + 1.
         create sptwkfl.
         assign
            element = spt_element
            primary = no
            prim2   = no
            ao      = spt_ao
            cst_tl  = spt_cst_tl
            cst_ll  = spt_cst_ll
            cst_tot = spt_cst_tl + spt_cst_ll
            part    = sct_part
            seq     = i.
         if spt_pct_ll = truncate(spt_pct_ll,0) then
            assign
               primary = yes
               prim2   = yes.
         cat_desc = usr_val[integer(truncate(spt_pct_ll,0))].
         release spt_det.
      end.

      find first sptwkfl no-error.
      if not available sptwkfl then do i = 1 to 5:
         create sptwkfl.
         assign
            element  = usr_elm[i]
            primary  = yes
            prim2    = yes
            ao       = no
            cst_tl   = 0
            cst_ll   = 0
            cst_tot  = 0
            part     = sct_part
            seq      = i
            cat_desc = usr_val[i].
      end.

      for each sptwkfl break by part:
         accumulate cst_tl (total by part).
         accumulate cst_ll (total by part).
         accumulate cst_tot (total by part).
      end.

      create ttTotal.
      assign
         ttTotal.tot_tl       = accum total cst_tl
         ttTotal.tot_ll       = accum total cst_ll
         ttTotal.cst_date     = sct_cst_date
         ttTotal.site         = in_site
         ttTotal.csset        = csset
         ttTotal.ref_id       = unique_id_part.

      for each sptwkfl by seq with frame d:
         create ttCostInfo.
         assign
            unique_id_cost       = unique_id_cost + 1
            ttCostInfo.element   = sptwkfl.element
            ttCostInfo.cst_tl    = sptwkfl.cst_tl
            ttCostInfo.is_primary
                                 = sptwkfl.primary
            ttCostInfo.cst_ll    = sptwkfl.cst_ll
            ttCostInfo.ao        = sptwkfl.ao
            ttCostInfo.cat_desc  = sptwkfl.cat_desc
            ttCostInfo.unique_id = unique_id_cost
            ttCostInfo.ref_id    = unique_id_part
         .
      end. /* for each sptwkfl */
   end. /* IF AVAILABLE sct_det ... */
END PROCEDURE. /* PROCEDURE CreateCostDetailAndTotal */
